#!/use/bin/python
# coding=utf-8
# 主动服务 开机率 analysis_startup_rate  需要执行一次
# type,1已安装生活助手2已安装生活助手且订阅了服务3已安装生活助手但没有订阅服务
import datetime
import pymysql

from dbutils.pooled_db import PooledDB


# proactive_service_conf 数据源
def getConfConnection():
    # 开发环境
    # pool = PooledDB(pymysql, 1, host='172.20.135.96', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_conf',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                db='proactive_service_conf', port=3307)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# proactive_service_data 数据源
def getDataConnection():
    # 开发环境
    #pool = PooledDB(pymysql, 1, host='172.20.151.80', user='pushdb', passwd='SkYWOrTh$TcOs',
    #                db='proactive_service_data',
    #                port=3306)  # 1为连接池里的最少连接数
    # 测试环境
    pool = PooledDB(pymysql, 1, host='172.20.154.103', user='test_dmp', passwd='DghHC3lFM1KzT3ZJ',
                    db='proactive_service_data', port=3407)  # 1为连接池里的最少连接数
    # pool = PooledDB(pymysql,1,host='127.0.0.1',user='root',passwd='root',db='life_assistant_data',port=3306) # 5为连接池里的最少连接数
    conn = pool.connection()
    cur = conn.cursor()
    return conn, cur

# 记录 数据分析-主动服务-开机率对比 analysis_startup_rate  已安装生活助手
def insertStartupRateTyep1(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_startup_rate 表是否已经存在当天 开机率数据
        selectCountSql = """
                    select count(1) num from analysis_startup_rate t where 1=1 and t.ref_date =  '%s' and type = 1
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        #获取 获取已安装生活助手的设备
        conn1, cur1 = getConfConnection()
        selectNumSql = """
                    select count(DISTINCT(t.active_id)) from life_assistant_device t
                """;
        cur1.execute(selectNumSql)
        resultNum = cur1.fetchone()
        #计算 当日开机设备量
        selectStartupNumSql = """
                    select count(DISTINCT(t.active_id)) from log_sys_poweron t  where  DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' 
                """ % (date)
        cur.execute(selectStartupNumSql)
        resultStartupNum = cur.fetchone()
        # 计算开机率
        startupRate = 0
        startupNum = resultStartupNum[0]
        pushNum = resultNum[0]
        if pushNum!=0:
            startupRate = round(startupNum*100/pushNum,2)

        if countNum == 0:
            logDetail = "新增-%s日安装生活助手的设备数量为%s,开机设备量为%s,开机率为%s" % (date,pushNum,startupNum,startupRate)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_startup_rate(startup_rate,startup_num,push_num,ref_date,type) 
                value(%s,%s,%s,'%s',%s)""" % (startupRate,startupNum,pushNum,date,1)
            cur.execute(insertSql)
        else:
            logDetail = "更新-%s日安装生活助手的设备数量为%s,开机设备量为%s,开机率为%s" % (date,pushNum,startupNum,startupRate)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                update analysis_startup_rate set startup_rate = %s ,startup_num = %s,push_num = %s ,update_time = now()  where
                    ref_date = '%s' and type = 1
            """ % (startupRate,startupNum,pushNum,date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 数据分析-主动服务-开机率对比 analysis_startup_rate  已安装生活助手且订阅了服务
def insertStartupRateTyep2(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_startup_rate 表是否已经存在当天 开机率数据
        selectCountSql = """
                    select count(1) num from analysis_startup_rate t where 1=1 and t.ref_date =  '%s' and type = 2 and execute_flag = 1 
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        #获取 已安装生活助手且订阅了服务(三个服务任一一个)
        conn1, cur1 = getConfConnection()
        selectNumSql = """
                    select count(DISTINCT(t.active_id)) subNum from life_assistant_device t where (t.zjkj = 1 and t.zjkj_ui =1) 
                        or (t.jkzx = 1 and t.jkzx_ui =1 and t.jkzx_execute_flag = 1) or (t.yjyl = 1 and t.yjyl_ui =1 and t.yjyl_execute_flag = 1)
                """;
        cur1.execute(selectNumSql)
        resultNum = cur1.fetchone()
        selectSql = """
                            select DISTINCT(t.active_id) activeId from life_assistant_device t where (t.zjkj = 1 and t.zjkj_ui =1) 
                                or (t.jkzx = 1 and t.jkzx_ui =1 and t.jkzx_execute_flag = 1) or (t.yjyl = 1 and t.yjyl_ui =1 and t.yjyl_execute_flag = 1)
                        """;
        cur1.execute(selectSql)
        selectResult = cur1.fetchall()
        dateStr = "and t.active_id in ("
        for result in selectResult:
            activeId = result[0];
            dateStr = dateStr+"""'"""+activeId+"""',"""
        dateStr = dateStr[:-1] +")"
        print("激活id过滤条件:%s"%dateStr)
        #计算 当日开机设备量
        selectStartupNumSql = """
                    select count(DISTINCT(t.active_id)) from log_sys_poweron t  where  DATE_FORMAT(t.create_time, '%%Y-%%m-%%d') = '%s' %s
                """ % (date,dateStr)
        cur.execute(selectStartupNumSql)
        resultStartupNum = cur.fetchone()
        # 计算开机率
        startupRate = 0
        startupNum = resultStartupNum[0]
        pushNum = resultNum[0]
        if pushNum!=0:
            startupRate = round(startupNum*100/pushNum,2)

        if countNum == 0:
            logDetail = "新增-%s日安装生活助手且订阅了服务的设备数量为%s,开机设备量为%s,开机率为%s" % (date,pushNum,startupNum,startupRate)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_startup_rate(startup_rate,startup_num,push_num,ref_date,type,execute_flag) 
                value(%s,%s,%s,'%s',%s,1)""" % (startupRate,startupNum,pushNum,date,2)
            cur.execute(insertSql)
        else:
            logDetail = "更新-%s日安装生活助手且订阅了服务的设备数量为%s,开机设备量为%s,开机率为%s" % (date,pushNum,startupNum,startupRate)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                update analysis_startup_rate set startup_rate = %s ,startup_num = %s,push_num = %s ,update_time = now()  where
                    ref_date = '%s' and type = 2 and execute_flag = 1 
            """ % (startupRate,startupNum,pushNum,date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 记录 数据分析-主动服务-开机率对比 analysis_startup_rate  已安装生活助手但没有订阅服务
def insertStartupRateTyep3(date):
    try:
        conn, cur = getDataConnection()
        #判断 analysis_startup_rate 表是否已经存在当天 开机率数据
        selectCountSql = """
                    select count(1) num from analysis_startup_rate t where 1=1 and t.ref_date =  '%s' and type = 3 and execute_flag = 1 
                """ % date
        cur.execute(selectCountSql)
        result = cur.fetchone()
        countNum = result[0]
        #获取 已安装生活助手但没有订阅服务的设备
        selectType1Sql = """
                    select  t.push_num   from   analysis_startup_rate t where ref_date = '%s' and t.type = 1
                """ % (date);
        cur.execute(selectType1Sql)
        selectType1Result = cur.fetchone()
        selectType2Sql = """
                    select  t.push_num   from   analysis_startup_rate t where ref_date = '%s' and t.type = 2 and execute_flag = 1 
                """ % (date);
        cur.execute(selectType2Sql)
        selectType2Result = cur.fetchone()
        #计算 当日开机设备量
        selectStartupType1Sql = """
                            select  t.startup_num   from   analysis_startup_rate t where ref_date = '%s' and t.type = 1
                        """ % (date);
        cur.execute(selectStartupType1Sql)
        selectStartupType1Result = cur.fetchone()
        selectStartupType2Sql = """
                            select  t.startup_num   from   analysis_startup_rate t where ref_date = '%s' and t.type = 2 and execute_flag = 1 
                        """ % (date);
        cur.execute(selectStartupType2Sql)
        selectStartupType2Result = cur.fetchone()
        # 计算开机率
        startupRate = 0
        startupNum = selectStartupType1Result[0]-selectStartupType2Result[0]
        pushNum = selectType1Result[0]-selectType2Result[0]
        if pushNum!=0:
            startupRate = round(startupNum*100/pushNum,2)

        if countNum == 0:
            logDetail = "新增-%s日安装生活助手但没有订阅服务的设备数量为%s,开机设备量为%s,开机率为%s" % (date,pushNum,startupNum,startupRate)
            print(logDetail)
            # 插入早间服务可见设备量
            insertSql = """insert into analysis_startup_rate(startup_rate,startup_num,push_num,ref_date,type,execute_flag) 
                value(%s,%s,%s,'%s',%s,1)""" % (startupRate,startupNum,pushNum,date,3)
            cur.execute(insertSql)
        else:
            logDetail = "更新-%s日安装生活助手但没有订阅服务的设备数量为%s,开机设备量为%s,开机率为%s" % (date,pushNum,startupNum,startupRate)
            print(logDetail)
            # 更新早间服务可见设备量
            updateSql = """
                update analysis_startup_rate set startup_rate = %s ,startup_num = %s,push_num = %s ,update_time = now()  where
                    ref_date = '%s' and type = 3 and execute_flag = 1 
            """ % (startupRate,startupNum,pushNum,date);
            cur.execute(updateSql)
        saveLog(logDetail, date)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

# 获取当前日期
def todayYMD():
    today = datetime.datetime.now()-1
    # 获取想要的日期的时间
    re_date = (today).strftime('%Y-%m-%d')
    return re_date


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset).strftime('%Y-%m-%d')
    return re_date

#保存日志
def saveLog(detail,ref_date):
    try:
        conn, cur = getDataConnection()
        jobId = 28
        jobName = '核心指标-开机率(安装生活助手)(需要执行一次)'
        tableName = 'analysis_startup_rate'
        # 插入日志表
        saveLogSql = """insert into log_xxljob_analysis (job_id,job_name,detail,table_name,ref_date) 
                value(%s,'%s','%s','%s','%s')""" % (jobId,jobName,detail,tableName,ref_date)
        cur.execute(saveLogSql)
        conn.commit()
    except Exception as e:
        print(e)
    finally:
        cur.close()
        conn.close()

if __name__ == '__main__':
    #today = todayYMD()
    date = getdate(1)
    #today = "2021-12-13"
    print ("%s 日期,执行<核心指标-开机率(安装生活助手)(需需执行一次)>报表" % date)
    insertStartupRateTyep1(date)
    insertStartupRateTyep2(date)
    insertStartupRateTyep3(date)


